package  com.sxhalo.fsms.export;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.util.List;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;






@SuppressWarnings("deprecation")
public class ExcelUtils {
	
	static XSSFWorkbook wb;

	static CellStyle titleStyle; // 标题行样式
	static Font titleFont; // 标题行字体
	static CellStyle numStyle; // 标题行样式
	static Font numFont; // 标题行字体
	static CellStyle headStyle; // 表头行样式
	static Font headFont; // 表头行字体
	static CellStyle contentStyle; // 内容行样式
	static Font contentFont; // 内容行字体

	/**
	 * @Description: 生成Excel
	 */
	public static void export2Excel(ExportSetInfo setInfo) throws IOException {

		// 初始化
		init();
		// 生成sheet
		XSSFSheet sheet = getSheets(setInfo.getTitles());
		// 标题行
		createTableTitleRow(setInfo, sheet);
		// 表头
		creatTableHeadRow(setInfo, sheet);
		// 表体
		creatTableContentRow(setInfo, sheet);
		// 自动调整列宽
		adjustColumnSize(setInfo, sheet);
		// 将需要导出的数据输出到os
		wb.write(setInfo.getOut());
	}

	/**
	 * @Description: 生成XSSFWorkbook
	 */
	public static XSSFWorkbook createWorkBook(ExportSetInfo setInfo) {

		// 初始化
		init();
		// 生成sheet
		XSSFSheet sheet = getSheets(setInfo.getTitles());
		// 标题行
		createTableTitleRow(setInfo, sheet);
		// 报表编号
		createTableUnifiedNum(setInfo, sheet);
		// 表头
		creatTableHeadRow(setInfo, sheet);
		// 表体
		creatTableContentRow(setInfo, sheet);
		// 自动调整列宽
		adjustColumnSize(setInfo, sheet);

		return wb;
	}

	/**
	 * @Description: 初始化
	 */
	protected static void init() {
		wb = new XSSFWorkbook();

		titleStyle = wb.createCellStyle();
		titleFont = wb.createFont();
		numStyle = wb.createCellStyle();
		numFont = wb.createFont();
		headStyle = wb.createCellStyle();
		headFont = wb.createFont();
		contentStyle = wb.createCellStyle();
		contentFont = wb.createFont();

		initTitleCellStyle();
		initTitleFont();
		initNumberCellStyle();
		initNumberFont();
		initHeadCellStyle();
		initHeadFont();
		initContentCellStyle();
		initContentFont();
	}

	/**
	 * @Description: 自动调整列宽
	 */
	protected static void adjustColumnSize(ExportSetInfo setInfo,
			XSSFSheet sheet) {
		for (int i = 0; i < setInfo.getFieldCount(); i++) {
			sheet.autoSizeColumn(i, true);
		}
	}

	/**
	 * @Description: 创建标题行
	 */
	protected static void createTableTitleRow(ExportSetInfo setInfo,
			XSSFSheet sheet) {
		int lastcol = setInfo.getFieldCount();
		CellRangeAddress titleRange = new CellRangeAddress(0, 0, 0,
				lastcol > 0 ? lastcol - 1 : lastcol);
		sheet.addMergedRegion(titleRange);
		XSSFRow titleRow = sheet.createRow(0);
		titleRow.setHeight((short) 800);
		XSSFCell titleCell = titleRow.createCell(0);
		titleCell.setCellValue(setInfo.getTitles());
		titleCell.setCellStyle(titleStyle);
	}

	protected static void createTableUnifiedNum(ExportSetInfo setInfo,
			XSSFSheet sheet) {
		String cellValue = "";
		int firstRow = 0;
		int lastRow = 0;
		int firstCol = 0;
		int lastCol = 0;

		for (int i = 0; i < setInfo.getUnifiedCount(); i++) {
			String[] unified = setInfo.getUnifiedNum().get(i);
			if (unified.length == 5) {
				cellValue = unified[0];
				try {
					firstRow = Integer.parseInt(unified[1]);
					lastRow = Integer.parseInt(unified[2]);
					firstCol = Integer.parseInt(unified[3]);
					lastCol = Integer.parseInt(unified[4]);
				} catch (Exception e) {

				}
			}

			CellRangeAddress numRange = new CellRangeAddress(firstRow, lastRow,
					firstCol, lastCol);
			sheet.addMergedRegion(numRange);
			XSSFRow numRow = sheet.createRow(firstRow);
			numRow.setHeight((short) 300);
			XSSFCell numCell = numRow.createCell(firstCol);
			numCell.setCellValue(cellValue);
			numCell.setCellStyle(numStyle);
		}
	}

	/**
	 * @Description: 创建表头行
	 */
	protected static void creatTableHeadRow(ExportSetInfo setInfo,
			XSSFSheet sheet) {
		int topCount = setInfo.getUnifiedCount() + 1;
		List<ExportSetInfo.HeadRange> headkist = setInfo.getHeadRange();
		for (int i = 0; i < headkist.size(); i++) {
			ExportSetInfo.HeadRange head = headkist.get(i);
			// 不需要合并不执行
			if (!(head.getFirstRow() == head.getLastRow() && head.getFirstCol() == head
					.getLastCol())) {
				CellRangeAddress headRange = new CellRangeAddress(
						head.getFirstRow() + topCount, head.getLastRow()
								+ topCount, head.getFirstCol(),
						head.getLastCol());
				sheet.addMergedRegion(headRange);
			}

			XSSFRow headRow = sheet.getRow(head.getFirstRow() + topCount);
			if (headRow == null) {
				headRow = sheet.createRow(head.getFirstRow() + topCount);
			}
			XSSFCell headCell = headRow.getCell(head.getFirstCol());
			if (headCell == null) {
				headCell = headRow.createCell(head.getFirstCol());
			}
			headCell.setCellValue(head.getValue());
		}
		// 设置样式
		for (int i = 0; i < setInfo.getHeadNames().size(); i++) {
			XSSFRow headRow = sheet.getRow(i + topCount);
			headRow.setHeight((short) 350);
			for (int j = 0; j < setInfo.getFieldNames().length; j++) {
				XSSFCell headCell = headRow.getCell(j);
				if (headCell == null) {
					headCell = headRow.createCell(j);
				}
				headCell.setCellStyle(headStyle);
			}
		}
	}

	/**
	 * @Description: 创建表数据行
	 */
	protected static void creatTableContentRow(ExportSetInfo setInfo,
			XSSFSheet sheet) {
		int topCount = setInfo.getUnifiedCount() + setInfo.getHeadCount() + 1;
		for (int i = 0; i < setInfo.getDatalist().size(); i++) {
			// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
			// 创建一行，在页sheet上
			XSSFRow dataRow = sheet.createRow(i + topCount);
			dataRow.setHeight((short) 300);
			// 在row行上创建一个方格
			for (int j = 0; j < setInfo.getFieldNames().length; j++) {
				String key = setInfo.getFieldNames()[j];
				Object value = setInfo.getDatalist().get(i).get(key);
				XSSFCell cell = dataRow.createCell(j);
				cell.setCellValue(value == null ? "" : value.toString());
				cell.setCellStyle(contentStyle);
			}
		}
	}

	/**
	 * @Description: 创建所有的Sheet
	 */
	protected static XSSFSheet getSheets(String sheetName) {
		return wb.createSheet(sheetName);
	}

	/**
	 * @Description: 初始化标题行样式
	 */
	protected static void initTitleCellStyle() {
		titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
		titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		titleStyle.setFont(titleFont);
	}

	/**
	 * @Description: 初始化表头行样式
	 */
	protected static void initHeadCellStyle() {
		headStyle.setAlignment(CellStyle.ALIGN_CENTER);
		headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		headStyle.setFont(headFont);
		headStyle.setBorderTop(CellStyle.BORDER_THIN);
		headStyle.setBorderBottom(CellStyle.BORDER_THIN);
		headStyle.setBorderLeft(CellStyle.BORDER_THIN);
		headStyle.setBorderRight(CellStyle.BORDER_THIN);
		headStyle.setTopBorderColor(IndexedColors.BLACK.index);
		headStyle.setBottomBorderColor(IndexedColors.BLACK.index);
		headStyle.setLeftBorderColor(IndexedColors.BLACK.index);
		headStyle.setRightBorderColor(IndexedColors.BLACK.index);
	}

	/**
	 * @Description: 初始化内容行样式
	 */
	protected static void initContentCellStyle() {
		contentStyle.setAlignment(CellStyle.ALIGN_CENTER);
		contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		contentStyle.setFont(contentFont);
		contentStyle.setBorderTop(CellStyle.BORDER_THIN);
		contentStyle.setBorderBottom(CellStyle.BORDER_THIN);
		contentStyle.setBorderLeft(CellStyle.BORDER_THIN);
		contentStyle.setBorderRight(CellStyle.BORDER_THIN);
		contentStyle.setTopBorderColor(IndexedColors.BLACK.index);
		contentStyle.setBottomBorderColor(IndexedColors.BLACK.index);
		contentStyle.setLeftBorderColor(IndexedColors.BLACK.index);
		contentStyle.setRightBorderColor(IndexedColors.BLACK.index);
		contentStyle.setWrapText(true); // 字段换行
	}

	/**
	 * @Description: 初始化编号行样式
	 */
	protected static void initNumberCellStyle() {
		numStyle.setFont(numFont);
	}

	/**
	 * @Description: 初始化标题行字体
	 */
	protected static void initTitleFont() {
		titleFont.setFontName("华文楷体");
		titleFont.setFontHeightInPoints((short) 14);
		titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		titleFont.setCharSet(Font.DEFAULT_CHARSET);
		titleFont.setColor(IndexedColors.BLACK.index);
	}

	/**
	 * @Description: 初始化表头行字体
	 */
	protected static void initHeadFont() {
		headFont.setFontName("宋体");
		headFont.setFontHeightInPoints((short) 10);
		headFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		headFont.setCharSet(Font.DEFAULT_CHARSET);
		headFont.setColor(IndexedColors.BLACK.index);
	}

	/**
	 * @Description: 初始化内容行字体
	 */
	protected static void initContentFont() {
		contentFont.setFontName("宋体");
		contentFont.setFontHeightInPoints((short) 10);
		contentFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
		contentFont.setCharSet(Font.DEFAULT_CHARSET);
		contentFont.setColor(IndexedColors.BLACK.index);
	}

	/**
	 * @Description: 初始化编号行字体
	 */
	protected static void initNumberFont() {
		numFont.setFontName("宋体");
		numFont.setFontHeightInPoints((short) 10);
		numFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
		numFont.setCharSet(Font.DEFAULT_CHARSET);
		numFont.setColor(IndexedColors.BLACK.index);
	}

	public static void ExcelDownload(String fileName,
			HttpServletResponse response, ByteArrayOutputStream os)
			throws UnsupportedEncodingException, IOException {
		byte[] content = os.toByteArray();
		InputStream is = new ByteArrayInputStream(content);
		// 设置response参数，可以打开下载页面
		response.reset();
		response.setContentType("application/vnd.ms-excel;charset=utf-8");
		response.setHeader("Content-Disposition", "attachment;filename="
				+ new String((fileName + ".xlsx").getBytes(), "iso-8859-1"));
		ServletOutputStream out = response.getOutputStream();
		BufferedInputStream bis = null; // 创建了一个内部缓冲区数组
		BufferedOutputStream bos = null;
		try {
			bis = new BufferedInputStream(is);
			bos = new BufferedOutputStream(out);
			byte[] buff = new byte[2048];
			int bytesRead;
			while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
				bos.write(buff, 0, bytesRead);
			}
		} catch (final IOException e) {
			throw e;
		} finally {
			if (bis != null)
				bis.close();
			if (bos != null)
				bos.close();
		}
	}

}